﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Claro.QuestionModel
{
    public class clsAluno
    {
        public int codAluno { get; set; }
        public string nmAluno { get; set; }
        public clsCurso curso { get; set; }

        public static List<clsAluno> SelecionarAlunosPorCurso(int codCurso)
        {
            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = "SELECT codAluno, nmAluno from aluno where codCurso = @codCurso";
            cmd.Parameters.Add("@codCurso", SqlDbType.Int).Value = codCurso;
            SqlDataReader dr = cmd.ExecuteReader();
            List<clsAluno> Alunos = new List<clsAluno>();
            while (dr.Read())
            {
                clsAluno aluno = new clsAluno();
                aluno.codAluno = (int)dr["codaluno"];
                aluno.nmAluno = dr["nmaluno"].ToString();
                Alunos.Add(aluno);
            }
            cn.Close();
            cn.Dispose();
            return Alunos;        
        }

        public static List<clsAluno> SelecionarAlunosGrupo(int codgrupo)
        {
            string sql = " select A.codAluno, A.nmAluno" +
                         " from Aluno A inner join AlunoGrupo AG" +
                         " on A.codAluno = AG.codAluno" +
                         " where AG.codGrupo = @codgrupo";
            SqlConnection cn = clsConexao.Conectar();
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@codGrupo", SqlDbType.Int).Value = codgrupo;
            SqlDataReader dr = cmd.ExecuteReader();
            List<clsAluno> Alunos = new List<clsAluno>();
            while (dr.Read())
            {
                clsAluno aluno = new clsAluno();
                aluno.codAluno = (int)dr["codaluno"];
                aluno.nmAluno = dr["nmaluno"].ToString();
                Alunos.Add(aluno);
            }
            cn.Close();
            cn.Dispose();
            return Alunos;  
        }

    }
}
